package com.jcb.countfrench.dao;

import java.util.ArrayList;
import java.util.List;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.util.Log;

import com.jcb.countfrench.R;
import com.jcb.countfrench.model.BasicData;
import com.jcb.countfrench.model.Quizz;
import com.jcb.countfrench.model.QuizzQuestionReponse;

public class CountFrenchDAO extends GlobalDAO {

	public CountFrenchDAO(Context context) {
		super(context);
	}
	
	//db.execSQL("CREATE TABLE dictionary (id_dictionary INTEGER
	//,num_dictionary INTEGER, mot_langue_ref TEXT, mot_trad TEXT,phon_langue_ref TEXT,phon_trad TEXT" +
	//		", traduction TEXT, example TEXT );");

	public static String[][] dictionary = 
		{
		//id ; mot ref ; mot trad ;ordre ; phon lgue ref ; phon trad ; 
			{"1-10", "1","1","1","1","yi", "un", "?", "1=1", "" + R.raw.n1}
			,{"1-10","2","2","2","2","yi", "deux", "?", "1=1", "" + R.raw.n2}
			,{"1-10","3","3","3","3","yi", "trois", "?", "1=1", "" + R.raw.n3}
			,{"1-10","4","4","4","4","yi", "quatre", "?", "1=1", "" + R.raw.n4}
			,{"1-10","5","5","5","5","yi", "cinq", "?", "1=1", "" + R.raw.n5}
			,{"1-10","6","6","6","6","yi", "six", "?", "1=1", "" + R.raw.n6}
			,{"1-10","7","7","7","7","yi", "sept", "?", "1=1", "" + R.raw.n7}
			,{"1-10","8","8","8","8","yi", "huit", "?", "1=1", "" + R.raw.n8}
			,{"1-10","9","9","9","9","yi", "neuf", "?", "1=1", "" + R.raw.n9}
		} ;

	public static String[][] quizz =
		{
			{ "1", "1", "Quizz 01" }
			,{ "2","2",  "Quizz 02" }
			,{ "3","3",  "Quizz 03" }
		} ;
	
	public static String[][] questionQuizz = 
		{
			//id ; id grpe; ordre ; p1    ;p2 ; p3;p4 ; sol 	;vide;vide;vide ;phon trad ; 
			{"1" , "1"		,"1"	,"4", "1","3","10"	,"1"	,"", "", "",  "" + R.raw.n1}
			,{"2", "1"		,"2"	,"5", "8","3","2"	,"5"	,"", "", "",  "" + R.raw.n5}
			,{"3", "1"		,"3"	,"3", "5","1","6"	,"6"	,"", "", "",  "" + R.raw.n6}
			,{"4", "1"		,"4"	,"5", "8","0","4"	,"8"	,"", "", "",  "" + R.raw.n8}
			,{"5" , "1"		,"5"	,"4", "1","3","10"	,"1"	,"", "", "",  "" + R.raw.n1}
			,{"6", "1"		,"6"	,"5", "1","2","3"	,"5"	,"", "", "",  "" + R.raw.n5}
			,{"7", "1"		,"7"	,"3", "6","1","9"	,"6"	,"", "", "",  "" + R.raw.n6}
			,{"8", "1"		,"8"	,"5", "8","0","2"	,"8"	,"", "", "",  "" + R.raw.n8}
			,{"9" , "1"		,"9"	,"4", "1","3","9"	,"9"	,"", "", "",  "" + R.raw.n1}
			,{"10", "1"		,"10"	,"5", "2","4","6"	,"5"	," ", "", "",  "" + R.raw.n5}
			
			
		} ;
	
	
	public void insertInitialData(SQLiteDatabase db) {
		Log.i("GlobalDAO", "insertInitialData...");
		
		 
		ContentValues cv = new ContentValues();
		
		Log.i("GlobalDAO", "insert Dictionary...");
		for (int i = 0 ; i < dictionary.length ; i++ ) {
			cv.put("id_dictionary", dictionary[i][1]);
			cv.put("num_dictionary", dictionary[i][0]);
			cv.put("ordre", dictionary[i][4]);
			cv.put("mot_langue_ref", dictionary[i][2]);
			cv.put("mot_trad", dictionary[i][3]);
			cv.put("phon_langue_ref", dictionary[i][5]);
			cv.put("phon_trad", dictionary[i][6]);
			cv.put("traduction", "1!");
			cv.put("example", "1+1");
			cv.put("sound", dictionary[i][9]);
			//cv.put("sound_trad", dictionary[i][2]);			
			db.insert("dictionary", "title", cv);
	}
		Log.i("GlobalDAO", "insert Dictionary OK...");
		
		cv = new ContentValues();
		Log.i("GlobalDAO", "insert questions Quizz...");
		for (int i = 0 ; i < questionQuizz.length ; i++ ) {
			cv.put("id_question", questionQuizz[i][0]);
			cv.put("id_quizz", questionQuizz[i][1]);
			cv.put("ordre", questionQuizz[i][2]);
			cv.put("proposition1", questionQuizz[i][3]);
			cv.put("proposition2", questionQuizz[i][4]);
			cv.put("proposition3", questionQuizz[i][5]);
			cv.put("proposition4", questionQuizz[i][6]);
			cv.put("solution", questionQuizz[i][7]);
			cv.put("phon_trad", questionQuizz[i][11]);
			db.insert("quizz_question", "title", cv);
			Log.i("GlobalDAO", "insert ligne quizz_question OK...");
		}

		Log.i("GlobalDAO", "insert quizz_question OK...");
		
		cv = new ContentValues();
		Log.i("GlobalDAO", "insert Quizz...");
		for (int i = 0 ; i < quizz.length ; i++ ) {
			cv.put("id_quizz", questionQuizz[i][0]);
			cv.put("ordre", questionQuizz[i][1]);
			cv.put("lib_quizz", questionQuizz[i][2]);
			cv.put("id_type_quizz", 1);
			db.insert("quizz", "title", cv);
			Log.i("GlobalDAO", "insert ligne quizz OK...");
		}
		
		Log.i("GlobalDAO", "insert quizz OK...");
		
	//db.execSQL("CREATE TABLE quizz_question (id_question INTEGER
	//,id_quizz INTEGER, ordre INTEGER, proposition1 TEXT, proposition2 TEXT, proposition3 TEXT, proposition4 TEXT,solution TEXT, phon_trad TEXT  );");

		
	}

	public BasicData getLastWordDictionary(BasicData basicData,String numDictionary) {
		return getWordDictionary (basicData,RULE_WORD.LAST,numDictionary );
	}		
	
	public BasicData getFirstWordDictionary(BasicData basicData, String numDictionary) {
		return getWordDictionary (basicData,RULE_WORD.FIRST,numDictionary );
	}	
	
	public BasicData getNextWordDictionary(BasicData basicData) {
		return getWordDictionary (basicData,RULE_WORD.NEXT,basicData.getNumDictionary() );
	}

	public BasicData getPrevWordDictionary(BasicData basicData) {
		return getWordDictionary (basicData,RULE_WORD.PREV,basicData.getNumDictionary() );
	}
	
	
	
	public QuizzQuestionReponse getNextQuestion(QuizzQuestionReponse prevQuizzQuestionReponse, int idQuizz) {
		QuizzQuestionReponse quizzQuestionReponse = null ;
		String operateur = ">" ;
		String sortOrder = "ASC" ;
		int ordre = 0  ;
		
		if (prevQuizzQuestionReponse != null) {
			ordre = prevQuizzQuestionReponse.getOrdre() ;
		}
		else {
			ordre = 0 ;
		}
		
		SQLiteDatabase db = this.getWritableDatabase();
		String requete = "select id_question" +
				",id_quizz " +
				",ordre" +
				",proposition1" +
				",proposition2" +
				",proposition3" + //5
				",proposition4" +
				",solution" +
				",phon_trad" 
		+ " FROM  quizz_question "
		+ " WHERE ordre " + operateur + ordre
		+ " ORDER by ordre " + sortOrder		
		;	
		
		Cursor cursor = db.rawQuery(requete, null);
		if (cursor.moveToNext()) {
			quizzQuestionReponse = new QuizzQuestionReponse(cursor.getInt(0));
			quizzQuestionReponse.setId_quizz(cursor.getInt(1));
			quizzQuestionReponse.setOrdre(cursor.getInt(2));
			List<String> listReponse = new ArrayList<String>();
			listReponse.add(cursor.getString(3));
			listReponse.add(cursor.getString(4));
			listReponse.add(cursor.getString(5));
			listReponse.add(cursor.getString(6));
			quizzQuestionReponse.setListReponses(listReponse);
			quizzQuestionReponse.setSolution(cursor.getString(7));
			quizzQuestionReponse.setSoundLangRef(cursor.getInt(8));
			Log.i("GlobalDAO", "getquestion " + quizzQuestionReponse.getId_question());			
		}
		else {
			Log.i("GlobalDAO", "getquestion RIEN" );
			Log.i("GlobalDAO", "requete=" + requete );	
		}		
		return quizzQuestionReponse ;
	}
	
	
	public BasicData getWordDictionary(BasicData basicData, RULE_WORD rule, String numDictionary) {
		BasicData nextBasicData = null ;
		int ordre = 0  ;
		String sortOrder = "";
		String operateur = "" ;
		
		switch (rule) {
			case NEXT : 
				operateur = " > " ;
				sortOrder = "ASC" ;
				ordre = basicData.getOrdre() ;	
				break ;
			case PREV :
				operateur = " < " ;
				sortOrder = "DESC" ;
				ordre = basicData.getOrdre() ;
				break ;
			case FIRST : 
				operateur = " = " ;
				ordre = 1 ;
				break ;
			case LAST :
				operateur = " < " ;
				ordre = 1000 ;
				sortOrder = "DESC" ;
				break ;
		}
		
		
		
		Log.i("GlobalDAO", "getNextWordDictionary...");
		SQLiteDatabase db = this.getWritableDatabase();
		String requete = "select id_dictionary," +
				" num_dictionary" +
				",ordre" +
				",mot_langue_ref" +
				",mot_trad" +
				",phon_langue_ref" + //5
				",phon_trad" +
				",traduction" +
				",example  " +
				",sound  "
		+ " FROM dictionary "
		+ " WHERE ordre " + operateur + ordre
		+ " ORDER by ordre " + sortOrder
		
		;	
		
		//id_dictionary INTEGER, ordre INTEGER,num_dictionary INTEGER, mot_langue_ref TEXT, mot_trad TEXT,phon_langue_ref TEXT,phon_trad TEXT" +
		//", traduction TEXT, example
		
		Cursor cursor = db.rawQuery(requete, null);
		if (cursor.moveToNext()) {
			nextBasicData = new BasicData(cursor.getInt(0));
			nextBasicData.setNumDictionary(cursor.getString(1));
			nextBasicData.setLibLangRef(cursor.getString(3));
			nextBasicData.setLibLangTad(cursor.getString(4));
			nextBasicData.setPhonetiquelibLangRef(cursor.getString(5));
			nextBasicData.setPhonetiquelibLangTrad(cursor.getString(6));
			nextBasicData.setSoundLangRef(cursor.getInt(9));
			nextBasicData.setOrdre(cursor.getInt(2));
			Log.i("GlobalDAO", "getword " + nextBasicData.getLibLangRef());			
		}
		else {
			Log.i("GlobalDAO", "getword RIEN" );
			Log.i("GlobalDAO", "requete=" + requete );	
		}
		return nextBasicData ; 
	}
	
	private enum RULE_WORD {
		 FIRST, PREV, NEXT, LAST;  //; is optional
		}
	
	
	public List<Quizz> getListQuizz() {
		List<Quizz> listquizz = new ArrayList<Quizz>();
		SQLiteDatabase db = this.getWritableDatabase();
		String requete = "select " +
				"id_quizz " +
				",ordre" +
				",lib_quizz" +
				"  FROM quizz order by ordre";
		Cursor cursor = db.rawQuery(requete, null);
		while (cursor.moveToNext()) {
			Quizz q = new Quizz(cursor.getInt(0));
			q.setNbQuestion(10);
			q.setLibQuizz(cursor.getString(2));
			listquizz.add(q);
		}
		return listquizz ;
	}
	
}
